
 CREATE        PROC SP_UPDATE_BOARD_KIT_INVENTORY_LOT (
 
 @date_in varchar (20),
 @qty_in varchar (20),
 @lot_id varchar (20),
 @lot_no varchar (20),
 @test_qty int,
 @rejected_qty int,
 @dateCode varchar(50)
 )
 
 
 as
 begin
 	if @date_in = '01/01/1900' 
 	begin
 		set @date_in = null
 	end
 
 	declare @temp_lot_no varchar(20)
	declare @temp_bal_qty varchar(20)
	declare @old_qty_In varchar(20)

 	select @temp_lot_no= Lot_Number from Lot_Table where Lot_ID = @lot_id
 	if @temp_lot_no <> @lot_no
 	begin
 		update Lot_Table set Lot_Number = @lot_no , Lot_Date_Code = @dateCode where Lot_ID = @lot_id 
 	end
 	select @temp_bal_qty = IL_Qty_Balance, @old_qty_In = IL_Qty_In from Inventory_Lot where Lot_Id = @lot_id
	if @temp_bal_qty = null
	begin
	 	update inventory_lot set IL_Date_In = @date_in, IL_Qty_In = @qty_In, IL_Test_Qty = @test_qty, IL_Rejected_Qty = @rejected_qty where Lot_ID = @lot_id
		update Lot_Table set Lot_Date_Code = @dateCode where Lot_ID = @lot_id 
	end
	else
	begin
		update inventory_lot set IL_Qty_Balance = ( (convert(int , @qty_In)-convert(int, @old_qty_In))+convert(int, @temp_bal_qty) ), IL_Date_In = @date_in, IL_Qty_In = @qty_In, IL_Test_Qty = @test_qty, IL_Rejected_Qty = @rejected_qty where Lot_ID = @lot_id
		update Lot_Table set Lot_Date_Code = @dateCode where Lot_ID = @lot_id 
	end
	
	
	---CLOSE THE LOT ONLY WHEN UNTESTEDQTY = PS SHIPPED QTY + PS ISSUED QTY + REJECTED QTY
	DECLARE @TOT_PS_SHIPPED_ISSUED_QTY INT
	DECLARE @UNTESTED_QTY INT
	--the following query returns the total shipped and issued qty for the lotid
	SELECT @TOT_PS_SHIPPED_ISSUED_QTY = ISNULL ( SUM(CSLNT.CSLNT_Qty_Pull), 0 )
		FROM Customer_Shipping_Lot_Number_Table CSLNT 
		INNER JOIN Customer_Shipping_Line_Item_Table CSLIT ON CSLNT.CSLIT_ID = CSLIT.CSLIT_ID 
		INNER JOIN Customer_Shipping_Table CST ON CSLIT.CustomerShipping_ID = CST.CustomerShipping_ID 
		INNER JOIN Lot_Table ON CSLNT.Lot_ID = Lot_Table.Lot_ID 
		WHERE (CST.CST_Status = 'ISSUED' OR CST.CST_Status = 'SHIPPED') AND (CSLNT.CSLNT_Qty_Pull > 0) 
		AND (CSLNT.Lot_ID = @lot_id)
		
	SET @UNTESTED_QTY = @test_qty
	IF ( @UNTESTED_QTY = ( @TOT_PS_SHIPPED_ISSUED_QTY + @rejected_qty ) )
	BEGIN
		UPDATE Lot_Table SET Lot_Current_Status = 'Close' WHERE Lot_ID = @lot_id
	END
 
 end

